<?php

/**
 * User: PuLinGao
 * Date: 2015/01/04
 * 处理问卷答案导入Model
 */
class QuestionBenchmarkImportModel {

    private $question_id;
    private $project_id;
    private $answerInduceTitle;
    private $questionDetail;
    private $questionNetwork;
    private $levelMapNetwork;
    private $buffer;
    private $subjectTable;
    private $subjectNames;
    private $existsNetQues = array();
    private $error;

    // 防止执行时间较长
    public function __construct() {
        ini_set("max_execution_time", 0);
        ini_set("memory_limit", "2048M");
    }

    //回答题型中，必须出现的表头信息
    public static $mustThField = array(
        'level_two_suggestion' => '分行反馈意见            （分行处理意见）',
        'level_two_isreturn' => '是否还分        （分行处理结果）',
        'level_two_dissent' => '分行提交状态',
        'level_three_suggestion' => '支行反馈意见            （支行处理意见）',
        'level_three_isreturn' => '是否还分        （支行处理结果）',
        'level_three_dissent' => '支行提交状态',
        'level_one_suggestion' => '总行建议',
        'level_one_last_time' => '总行最后提交时间',
        'sino_handle_suggestion' => '正达银友复核情况      （正达银友处理意见）',
        'sino_handle_isreturn' => '是否还分        （正达银友处理结果）',
        'sino_handle_isreturnvalue' => '还分值',
    );

    //模板中应存在的合法标题
    public static function legalField() {
        $mustTh = self::$mustThField;
        $forGet = self::$forGetField;
        return array_merge($mustTh, $forGet);
    }

    //模板中如果存在网点编号，网点名称等必填操作，则同时验证网点编号和网点名称两项
    public static $forGetField = array(
        'network_num' => '网点编号',
        'question_num' => '指标编码',
    );
    //excel中的等级对应
    public static $levelMap = array(
        'level_one' => 1,
        'level_two' => 2,
        'level_three' => 3,
    );
    
    // 改扣对应的关键字
    public static $change_buckle = array(
        'change' => '改',
        'buckle' => '扣',
    );

    /**
     * 设置错误信息，方便提示上传者
     * @param type $errNo 错误单元格编号
     * @param type $errMsg 错误信息
     */
    private function setError($errNo, $errMsg) {
        $this->error[] = array(
            'err_no' => $errNo,
            'err_msg' => $errMsg
        );
    }

    /**
     * 获取错误信息
     * @return type
     */
    public function getError() {
        return $this->error;
    }

    /**
     * 格式化excel数据
     * @param type $question_id 问卷ID
     * @param type $excelData	对标回答excel数据
     * @return boolean
     */
    public function formatExcelData($question_id, $excelData) {
        if (!$question_id) {
            throw new Exception("请指定问卷");
        }
        if (empty($excelData)) {
            throw new Exception("未获取到excel信息");
        }
        # 检测问卷内容是否合法
        $legal = $this->checkLegal($excelData);

        if (!$legal['ok']) {
            throw new Exception($legal['msg']);
        }

        $this->question_id = $question_id;

        //基础数据
        $this->subjectTable = QuestionSubject::model()->getListByBase($this->question_id);
        $this->subjectNames = $this->subjectCate();
        $this->questionDetail = QuestionBase::model()->find('question_id=' . $this->question_id);
        $this->project_id = $this->questionDetail->project_id;
        $this->questionNetwork = NetworkQuestion::model()->getQuestionNetwork($this->question_id, $this->project_id);
        $this->levelMapNetwork = $this->levelNetworkMap();
        
        //提取和分类数据库中的数据 
        $induceExcelData = $this->induceData($excelData);
        
        //答案合格性检测
        $mustFieldKyes = array_keys(self::$mustThField);
        
        foreach ($induceExcelData['network_question'] as $exRow => $data) {
            
            // 解析excel中的数据，判断分行，总行以及正达银友
            $tmpField = array();
            foreach ($mustFieldKyes as $oneField) {
                
                if ($induceExcelData[$oneField]) {
                    
                    $specialDataField = $induceExcelData[$oneField]['data'][$exRow];
                    /**
                     * 20150612需求，针对正达银友的改扣问题，单独处理
                     */
                    $tmpParse = $this->parseField($oneField, $specialDataField);
                    if (!empty($tmpParse) && $tmpParse['field'] != 'isreturnvalue') {
                        
                        // 针对isreturn的特殊处理开始
                        if ($tmpParse['return_value']) {
                            
                            # 1.如果在excel中【是否还分（正达银友经典处理结果）】处填写的是“否”或者没有填写，即使后面【还分值】有数字存在，此题目也是不还分的
                            if ($tmpParse['field'] == 'isreturn' && $tmpParse['pre'] == "sino_handle" && !$tmpField[$tmpParse['pre']]['isreturn']) {
                                continue;
                            }
                            
                            /**
                             * 2.改扣处理，如果在excel中【还分值】处填写的字符串格式如“A4”，要想生效，则必须【是否还分（正达银友经典处理结果）】处填写为“改 or 扣”，否则不生效
                             * 注意，此处有个坑，in_array函数，如果前面的值是true，则不管后面是什么数组，都返回true，因此这里使用 (string) 转一下
                             */
                            if ( in_array( (string)$tmpField[$tmpParse['pre']]['isreturn'], self::$change_buckle ) ) {
                                $tmpField[$tmpParse['pre']]['special'] = $tmpField[$tmpParse['pre']]['isreturn'];
                                
                            } else {
                                continue;  //此处这样使用，是为了不执行后面的操作
                            }
                        }
                        
                        $tmpField[$tmpParse['pre']][$tmpParse['field']] = $tmpParse['value'];
                    }
                }
            }
            
            # 特殊处理，正达银友经典意见，允许还分的，但是没有填写还分值的，取当前subject_id的标准分，标准分如果不是自然数，则将还分置为0
            if ($tmpField['sino_handle']['isreturn'] === true) { //这里防止和1混淆
                $questionNum = $data['question_num'];
                $subTable = $this->subjectTable[$questionNum];
                if (is_numeric($subTable['standard_score'])) {
                    $tmpField['sino_handle']['isreturn'] = $subTable['standard_score'];
                } else {
                    $tmpField['sino_handle']['isreturn'] = 0;
                }
            }
            
            /**
             * 2015年6月14日16:19:33 新加字段，来表示不同的显示内容
             * 放在去除各级网点之前，处理起来不容易遗漏
             */
            foreach ($tmpField as $strKey => $strData) {
                if (!isset($strData['special'])) {
                    if (isset($strData['isreturn'])) {
                        $tmpField[$strKey]['isreturn_type'] = $strData['isreturn'] ? "是" : "否";
                    }
                    
                } else {
                    $tmpField[$strKey]['isreturn_type'] = $strData['special'];
                }
            }
            
            //正达银友复核如果是“改”，“扣”的操作，则去除网点的各级操作
            foreach ($tmpField as $tmpK => $tmpD) {
                if ($tmpK == "sino_handle" && $tmpD['special']) {
                    unset($tmpField['level_two']);
                    unset($tmpField['level_one']);
                    unset($tmpField['level_three']);
                }
            }
            
            // 分析level和network_id
            foreach ($tmpField as $levelKey => $levelD) {
                $ins = array_merge($data, $levelD);
                unset($ins['up_network_id']);
                unset($ins['question_num']);
                unset($ins['network_num']);

                if ($levelKey == 'sino_handle') {
                    $this->buffer[$exRow]['manage'] = $ins;
                } else {
                    $upLevel = self::$levelMap[$levelKey];
                    $levelNet = $this->parseLevelNetId($data, $upLevel);
                    $ins = array_merge($ins, $levelNet);
                    $this->buffer[$exRow]['benchmark'][] = $ins;
                }
            }
        }
    }

    /**
     * 保存数据，数据入库
     */
    public function saveExcelData() {
        $status = true;
        $insert = array();
        $update = array();
        foreach ($this->buffer as $key => $data) {
            
            $nq_id = $data['manage']['nq_id'];
            $subject_id = $data['manage']['subject_id'];

            # 筛选出需要插入的记录
            $insert['manage'][] = $data['manage'];
            if ($data['benchmark']) {
                foreach ($data['benchmark'] as $chrKey => $chrData) {
                    $insert['benchmark'][] = $chrData;
                }
            }
            
            /**
             * 计算需要更新的分数
             */
            # 1.isreturn是自然数的，表示扣分和还分的
            if (is_numeric($data['manage']['isreturn'])) {
                $nsKey = $nq_id . "-" . $subject_id;
                if (!isset($update[$nsKey])) {
                    $update[$nsKey] = 0;
                }
                $update[$nsKey] = $data['manage']['isreturn'];
            }
            
            # 2.special存在的，理论上说，“扣”的分值已经在上面计算过了，防止出现错误，重新计算一下“扣”的
            if ($data['manage']['special']) {
                if ($data['manage']['special'] == self::$change_buckle['change']) {
                    $nsKey = $nq_id . "-" . $subject_id;
                    if (!isset($update[$nsKey])) {
                        $update[$nsKey] = 0;
                    }
                    $subjectNum = '';
                    foreach ($this->subjectTable as $value) {
                        if ($value['subject_id'] == $data['manage']['subject_id']) {
                            $subjectNum = $value['question_num'];
                            break;
                        }
                    }
                    $tmpStandScore = is_numeric($this->subjectTable[$subjectNum]['standard_score']) ? $this->subjectTable[$subjectNum]['standard_score'] : 0;
                    $update[$nsKey] = $tmpStandScore;
                }
                
                if ($data['manage']['special'] == self::$change_buckle['buckle']) {
                    $nsKey = $nq_id . "-" . $subject_id;
                    if (!isset($update[$nsKey])) {
                        $update[$nsKey] = 0;
                    }
                    $update[$nsKey] = is_numeric($data['manage']['isreturn']) ? $data['manage']['isreturn'] : 0;
                }
            }
            
        }
//        echo '<pre  style="color:#9B0067;font-size:13px;">';
//        echo 'buffer';
//        print_r($this->buffer);
//        echo '</pre>';
//
//        echo '<pre  style="color:#9B0067;font-size:13px;">';
//        echo "insert";
//        print_r($insert);
//        echo '</pre>';
//
//        echo '<pre  style="color:#9B0067;font-size:13px;">';
//        echo 'update';
//        print_r($update);
//        echo '</pre>';
//        exit;
        
        # 1.benchmark 入库
        if ($insert['benchmark']) { // benchmark
            foreach ($insert['benchmark'] as $value) {
                $questionBenchmak = new QuestionBenchmark();
                
                # 先查询
                $isExists = $questionBenchmak->findAllByAttributes(array(
                    'nq_id' => $value['nq_id'],
                    'network_id' => $value['network_id'],
                    'level' => $value['level'],
                    'subject_id' => $value['subject_id'],
                ));
                
                # 如果存在则更新，不存在，就插入
                if (!empty($isExists)) {
                    $isExists = array_pop($isExists);
                    $isExistsArr = $isExists->attributes;
                    $value['update_time'] = date("Y-m-d H:i:s");
                    $questionBenchmak->updateByPk($isExistsArr['qb_id'], $value);
                    $qb_id = $isExistsArr['qb_id'];
                } else {
                    foreach ($value as $field => $v) {
                        $questionBenchmak->$field = $v;
                    }
                    $saveSta = $questionBenchmak->save();
                    if ($saveSta) {
                        $qb_id = $questionBenchmak->attributes['qb_id'];
                    }
                }
                $status = $status && $qb_id;
            }
        }

        # 2.benchmark_manage 入库
        if ($insert['manage']) { // benchmark_manage
            foreach ($insert['manage'] as $value) {
                
                //默认值取值
                unset($value['network_id']);
                unset($value['level']);
                if($value['special']){
                    unset($value['special']);
                }
                $value['user_id'] = 0;
                $value['truename'] = '最终结果';

                $questionBenchmarkManage = new QuestionBenchmarkManage();

                # 先查询
                $isExists = $questionBenchmarkManage->findAllByAttributes(array(
                    'nq_id' => $value['nq_id'],
                    'subject_id' => $value['subject_id'],
                ));

                # 如果存在则更新，不存在，就插入
                if (!empty($isExists)) {
                    $isExists = array_pop($isExists);
                    $isExistsArr = $isExists->attributes;
                    $value['update_time'] = date("Y-m-d H:i:s");
                    $questionBenchmarkManage->updateByPk($isExistsArr['qbm_id'], $value);
                    $qbm_id = $isExistsArr['qbm_id'];
                } else {
                    foreach ($value as $field => $v) {
                        $questionBenchmarkManage->$field = $v;
                    }
                    $saveSta = $questionBenchmarkManage->save();
                    if ($saveSta) {
                        $qbm_id = $questionBenchmarkManage->attributes['qbm_id'];
                    }
                }
                $status = $status && $qbm_id;
            }
        }

        # 3.更新问卷总得分
        if ($update) {
            foreach ($update as $key => $score) {
                list($nq_id, $subject_id) = explode('-', $key);
                // 修改题目分数（A1标准得分2分，实际得分1，还分2分，还分后就是2分 而不是2+1分）
                $answer = NetworkQuestionAnswer::model()->findByAttributes(array('nq_id' => $nq_id, 'subject_id' => $subject_id));
                $answer->answer_score = $score;
                $answer->save();

                /**
                 * 修改总分数
                 * 累加subject表中标准分是数值的subject_id的answer_score，用于更改networkQuestion的总分
                 */
                $allAnswerScores = NetworkQuestionAnswer::model()->findAll(array(
                    'select' => array('answer_score', 'nq_id', 'subject_id', 'question_id'),
                    'condition' => 'nq_id=:nq_id',
                    'params' => array(':nq_id' => $nq_id)
                ));
                $newTotalScores = 0;
                foreach ($allAnswerScores as $value) {
                    foreach ($this->subjectTable as $subject) {
                        if ($value['subject_id'] == $subject['subject_id']) {
                            if (is_numeric($subject['standard_score'])) {
                                $newTotalScores += $value['answer_score'];
                            }
                        }
                    }
                }
                $netquestion = NetworkQuestion::model()->findByPk($nq_id);
                $netquestion->score = $newTotalScores;
                $netquestion->save();
            }
        }
        return $status;
    }

    /**
     * 解析excel中的字段，对应到数据库中
     * @param type $field
     * @param type $value
     * @return type
     */
    public function parseField($field, $value) {
        if (!$field) {
            return array();
        }
        
        // 解析支行，分行，总行对应的字段前缀，如sino_handle , isreturn, suggestion 等
        $result = array();
        $returnValue = false;
        $tmps = explode('_', $field);
        $pre_field = join('_', array($tmps[0], $tmps[1]));
        $realField = str_replace($pre_field . '_', '', $field);

        //判断value的值，
        $realValue = $value['title'];
        if (in_array($realValue, array("是", "否"))) {          # 这里取是否，是为了兼容分行和支行中的“是否还分        （x行处理结果）”，因为值在数据库中取值为isreturn：1 or 0
            $realValue = ($realValue == "是") ? true : false;
        }
        
        /**
         * 为了兼容正达银友的格式，因为正达银友中存在还分值是多少，正达银友的处理结果在入库时，是根据还分值的多少来确定isreturn的状态，
         * 因此这里如果字段为还分值的字段，且是自然数，则excel还分值对应列的数据，取代isreturn 的值来入库
         */
        if ($realField == 'isreturnvalue'){
            if (is_numeric($realValue) || is_string($realValue)) { // 如果还分值的值对应自然数或者字符串，则表示是需要处理的值，分别对应0.25 or A4
                $realField = 'isreturn';
                $returnValue = true;
            }
        }
        
        return array(
            'pre' => $pre_field,
            'field' => $realField,
            'value' => $realValue,
            'return_value' => $returnValue
        );
    }

    /**
     * 根据当前的网点，查找其上级的对应level的网点
     * @param type $self
     * @param type $upLevel
     */
    public function parseLevelNetId($self, $upLevel) {
        $return = array(
            'level' => $upLevel,
            'network_id' => $self['network_id']
        );
        // 如果相等
        $selfLevel = $self['level'];
        if ($selfLevel == $upLevel) {
            $return['level'] = $selfLevel;
            $return['network_id'] = $self['network_id'];
            return $return;
        }

        // 如果是总行
        if ($upLevel == NetWork::NETWORK_LEVEL_ONE) {
            foreach ($this->levelMapNetwork as $key => $value) {
                if ($value['level'] == $upLevel) {
                    $return['network_id'] = $value['network_id'];
                    break;
                }
            }
            return $return;
        }

        // 如果正常
        $upData = $this->levelMapNetwork[$self['up_network_id']];
        
        # 如果当前获取的level不是对应的网点的上级level，则继续往上取，满足同一个网点，获取支行和分行的情况，因为本身和总行，已经在前面处理了
        if ($upData['level'] != $upLevel) {
            $upData = $this->levelMapNetwork[$upData['up_network_id']];
        }
        
        if (!empty($upData)) {
            $return['network_id'] = $upData['network_id'];
        }
       
        return $return;
    }

    /**
     * 正式解析数据之前，验证用户上传的是否是按照规定的模板生成的数据
     * @param type $excelData
     * @return boolean
     */
    public function checkLegal($excelData) {
        $return = array(
            'ok' => false,
            'msg' => ''
        );

        if (!$excelData['data_th'] || !is_array($excelData['data_th'])) {
            $return['msg'] = "未能成功检测到必要的问卷回答表头信息，如（" . join(',', self::legalField()) . "）等，请检查上传模板是否正确";
            return $return;
        }
        $dataTh = array();
        $tmpDataTh = array_pop($excelData['data_th']);
        foreach ($tmpDataTh as $key => $data) {
            $dataTh[] = $data['title'];
        }
        $check = false;
        foreach (self::legalField() as $value) {
            foreach ($dataTh as $data) {
                $check |= substr_count($data, $value);
            }
        }
        if (!$check) {
            $return['msg'] = "未能成功检测到必要的问卷回答表头信息，如（" . join(',', self::legalField()) . "）等，请检查上传模板是否正确";
            return $return;
        }

        return array(
            'ok' => true,
            'msg' => '合法'
        );
    }

    /**
     * 按照回答的题目类型，将excel数据归类整理
     * @param type $excelData
     * @return array
     */
    public function induceData($excelData) {
        $tmpData = $excelData['data_th'];
        $thData = array_pop($tmpData);
        $titleInduce = $this->resolveTitles($thData); //解析回答的表头，分类标识
        //保存解析的表头数据
        $this->answerInduceTitle = $titleInduce;

        $return = array();

        # 以列为单位获取回答数据
        $return = array();
        foreach ($titleInduce as $key => $data) {
            foreach ($excelData['data'] as $dataK => $dataD) { //回答data
                // 处理题目
                foreach ($data as $exCol => $exD) {

                    if (in_array($key, array_keys(self::$forGetField))) {
                        $return['nq_num']['data_th'][$exCol][$key] = $exD['title'];
                        $return['nq_num']['data'][$dataK][$key] = $dataD[$exCol];
                        continue;
                    }

                    if ($key == "level_one_last_time" && $dataD[$exCol]['date_time_status']) {
                        $this->setError($dataD[$exCol]['coordinate'], '“ ' . $exD['title'] . ' ” 下的时间数据未能正确解析，<b style="color:red">请在excel中将单元格设置为文本格式【重要：为了解析数据的准确，请将excel中所有带有“日期”或者“时间”的单元格都设置成文本格式】</b>');
                    }

                    $return[$key]['data_th'][$exCol] = $exD['title'];
                    $return[$key]['data'][$dataK] = $dataD[$exCol];
                }
            }
        }

        # 分离【网点编号】和【指标编码】
        $realRes = array();
        foreach ($return['nq_num']['data'] as $exRow => $exD) {
            //获取信息，验证网点和题目编号是否存在
            $nNum = $exD['network_num']['title'];
            $qNum = $exD['question_num']['title'];
            $nqAndNum = $nNum . $qNum;

            if (!isset($this->questionNetwork[$nNum])) {
                $this->setError($exD['network_num']['coordinate'], "当前excel中的网点编号“" . $nNum . "”在当前上传的问卷中未找到！");
            }

            $formalQNums = array_keys($this->subjectNames['formal']);
            if (!in_array($qNum, $formalQNums)) {
                $this->setError($exD['question_num']['coordinate'], "当前excel中的指标编码“" . $qNum . "”在当前上传的问卷中未找到！");
            }

            if (isset($this->existsNetQues[$nqAndNum])) {
                $coordinate = $exD['network_num']['coordinate'] . '--' . $exD['question_num']['coordinate'];
                $this->setError($coordinate, "当前excel中的网点编号和指标编码的组合“" . $nNum . "”，“" . $qNum . "”在同一个excel中已经出现过了！");
            }
            $this->existsNetQues[$nqAndNum] = $nqAndNum;

            $realRes[$exRow] = array(
                'network_num' => $nNum,
                'question_num' => $qNum,
                'nq_id' => $this->questionNetwork[$nNum]['nq_id'],
                'network_id' => $this->questionNetwork[$nNum]['network_id'],
                'up_network_id' => $this->questionNetwork[$nNum]['up_network_id'],
                'level' => $this->questionNetwork[$nNum]['level'],
                'subject_id' => $this->subjectTable[$qNum]['subject_id'],
            );
        }

        $return['network_question'] = $realRes;
        return $return;
    }

    /**
     * 解析回答的表头信息，按照题目分类
     * @param type $thData
     * @return array
     */
    public function resolveTitles($thData) {
        $induceTitle = array();
        $tmp = $this->subjectNames;

        $repeat = array(
            'base' => array(),
            'formal' => array()
        );
        foreach ($thData as $key => $value) {
            $data = $value['title'];
            $keys = self::legalField();
            $tableKey = array_search($data, $keys);
            if ($tableKey) {
                $induceTitle[$tableKey][$key] = array(
                    'title' => $data,
                    'coordinate' => $value['coordinate']
                );
            }
        }

        return $induceTitle;
    }

    /**
     * 对数据库中的数据进行分类，方便题型的检测
     * @return array
     */
    private function subjectCate() {
        $subjectNames = array();
        if (!$this->subjectTable) {
            return $subjectNames;
        }
        $tmp = array();
        foreach ($this->subjectTable as $item) {
            if ($item['is_basic']) {
                $subjectNames['base'][$item['question_num']] = $item['title'];
            } else {
                $subjectNames['formal'][$item['question_num']] = $item['title'];
            }
        }
        //分离【例行检查】 和 【好的地方】
        foreach ($subjectNames['formal'] as $num => $title) {
            preg_match_all('/^(ET_\d+)_(\d+)$/i', $num, $matches);
            if ($matches[0][0]) {
                $subjectNames['ext'][$matches[1][0]][$matches[2][0]] = $title;
                unset($subjectNames['formal'][$num]);
            }
        }

        return $subjectNames;
    }

    /**
     * 对数据库中的网点，按照最小的级别分析出其对应的不同级别的网点信息
     * @return array
     */
    private function levelNetworkMap() {
        $return = array();
        $new = array();
        foreach ($this->questionNetwork as $nNum => $nD) {
            $new[$nD['network_id']] = $nD;
        }
        
        # 因为network_question表中只保存了level为4的网点数据，这里需要使用到所有的网点信息，因此递归获取到所有的网点信息
        foreach ($new as $nID => $nD) {
            $up_network_id = $nD['up_network_id'];
            
            # 更改获取上级网点的方式
            if ($up_network_id) {
                $extData = $this->getAllNetwork($up_network_id);
                if (!empty($extData)) {
                    foreach ($extData as $k => $v) {
                        $new[$k] = $v;
                    }
                }
            }
        }
        
        # 分析各个网点的上级网点
        foreach ($new as $nID => $nD) {
            $return[$nID] = $nD;
            $up_network_id = $nD['up_network_id'];
            if ($nD['level'] == NetWork::NETWORK_LEVEL_ONE) {
                $return[$nID]['ups'] = array();
                continue;
            }
            
            if ($new[$up_network_id]) {
                $upLevel = $new[$up_network_id]['level'];
                $return[$nID]['ups'][$upLevel] = $new[$up_network_id];
            }
        }
        
        return $return;
    }
    
    /**
     * 递归获取所有的网点
     * @param type $up_network_id
     * @param array $array
     * @return type
     */
    private function getAllNetwork($up_network_id, & $array = array()) {
        if (!$up_network_id) {
            return array();
        }
        
        $upData = Network::model()->findByPk($up_network_id)->attributes;
        $array[$up_network_id] = $upData;
        if ($upData['up_network_id']) {
            $this->getAllNetwork($upData['up_network_id'], $array);
        }
        return $array;
    }

}
